﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.IO;
using YYNY.Tools.Dao;
using NPOI.SS.Util;
using NPOI.SS.Converter;
using NPOI.XSSF.Util;
using System.Drawing;
using System.Web.Mvc;
using YYNY.Controllers;

namespace YYNY.Tools
{
    public class ExcelTool
    {
        BaseDao dao = new BaseDao();
        public static string ImportInveAnalysis(string FilePath,string ProjectId)
        {
            FileStream fs = null;
            IWorkbook workbook = null;  //new HSSFWorkbook()
            ISheet sheet = null;
            //IRow row = null;
            //ICell cell = null;
            int RowCount = 0;
            int CellCount = 0;
            int? CodeColNum = null; //物料编码所在列
            int? SpecColNum = null; //规格名称所在列
            int? UnitColNum = null; //单位所在列
            int? RequColNum = null; //erp提报量所在列



            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            fs = File.OpenRead(FilePath);     //这里有点问题，需要找到具体的获取文件目录的地方

            if (FilePath.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2007版本excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (FilePath.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2003版本excel
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                //workbook.Close();
                fs.Close();
                return "错误的文件类型";     //这里要返回错误值；
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);  //读取第一个sheet
                }
                else
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";   //这里返回错值
                }

                if (sheet == null)
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";     //这里返回错值
                }
                RowCount = sheet.LastRowNum;
                CellCount = sheet.GetRow(0).LastCellNum;

                
                //这里判断表格表头内容
                IRow TempRow = sheet.GetRow(0);
                for (int i = 0; i < CellCount; i++)
                {
                    if (TempRow.GetCell(i).ToString() == "物料")
                    {
                        CodeColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "物料描述")
                    {
                        SpecColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "计量单位")
                    {
                        UnitColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "申请数量")
                    {
                        RequColNum = i;
                    }
                }
                if (!CodeColNum.HasValue || !SpecColNum.HasValue || !UnitColNum.HasValue || !RequColNum.HasValue)
                {
                    workbook.Close();
                    fs.Close();
                    return "表头有错误";     //表格错误
                }
                double TempDouble = 0;
                for (int i = 1; i < RowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        ICell cell = row.GetCell(CodeColNum.Value);
                        
                        if (!double.TryParse(cell.ToString(), out TempDouble))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 物料编码错误";
                        }
                        cell = row.GetCell(RequColNum.Value);
                        if (!double.TryParse(cell.ToString(), out TempDouble))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 申请数量错误";
                        }
                        cell = row.GetCell(SpecColNum.Value);
                        if (cell.ToString() == "")
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 物料描述错误";
                        }
                        cell = row.GetCell(UnitColNum.Value);
                        if (cell.ToString() == "")
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 计量单位错误";
                        }

                        Dictionary<string, string> map = new Dictionary<string, string>()
                        {
                            ["Code"] = row.GetCell(CodeColNum.Value).ToString(),
                            ["Specification"] = row.GetCell(SpecColNum.Value).ToString(),
                            ["RequAmount"] = row.GetCell(RequColNum.Value).ToString(),
                            ["Unit"] = row.GetCell(UnitColNum.Value).ToString(),
                            ["ProjectId"]= ProjectId,
                            ["IsDelete"]="0"
                        };
                        list.Add(map);
                    }
                }
                string sql = "begin ";
                for (int i = 0; i < list.Count; i++)
                {
                    sql += "insert into t_d_Inventory (Code,Specification,Unit,RequAmount,ProjectId,IsDelete) values ";
                    sql += "('" + list[i]["Code"] + "','" + list[i]["Specification"] + "','" + list[i]["Unit"] + "','"
                        + list[i]["RequAmount"] + "','"+ list[i]["ProjectId"]+"','0');";
                }
                sql += " end";

                BaseDao.execute(sql);

            }
            catch (Exception e)
            {
                workbook.Close();
                fs.Close();
                return e.Message.ToString().Replace("'", "");
                throw;
            }

            //完了后一定要注意释放流句柄
            workbook.Close();
            fs.Close();




            return "OK";
        }


        public XSSFWorkbook CivilPrice(string[] Ids)
        {

            string temp = "";
            int currRow = 0;
            int tempRow = 0;
            int i1 = 0;
            string fileName = "土建核价单 " + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ff");
            //创建07版Excel文件对象
            XSSFWorkbook book = new XSSFWorkbook();
            ISheet sheet1 = book.CreateSheet("Sheet1");

            XSSFCellStyle fCellStyle = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = book.CreateDataFormat();
            fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式,#表示空位保留
            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;

            string sql = "";
            for (int ii = 0; ii < Ids.Length; ii++)
            {

                sql = "select * from t_d_CivilPrice where CivilPriceId=" + Ids[ii];
                var Map1 = dao.GetList(sql, new string[] { "CivilPriceId", "Name", "TaxRate", "Unit", "UnitPrice", "Memo" })[0];
                sql = "select XMMC,GCLJSS,DW,GCL,RGDJ,GCL*RGDJ as RGHJ,CLDJ,GCL*CLDJ as CLHJ,GCL*(RGDJ+CLDJ) AS ZJ,Memo from t_d_CivilPriceSingle where CivilPriceId=" + Ids[ii] + " order by id asc";
                var List1 = dao.GetList(sql, new string[] { "XMMC", "GCLJSS", "DW", "GCL", "RGDJ", "RGHJ", "CLDJ", "CLHJ", "ZJ", "Memo" });

                IRow row0 = sheet1.CreateRow(currRow);   //创建行
                currRow++;
                ICell cell = row0.CreateCell(0);                     //创建列

                temp = Map1["Name"].ToString();
                cell.SetCellValue(temp);
                setCellStyle(book, cell);
                sheet1.AddMergedRegion(new CellRangeAddress(currRow-1, currRow-1, 0, 10));
                //创建表头
                string[] firstName = { "序号", "项目名称", "工程量计算式", "单位", "工程量", "人工费", "", "材料费", "", "总价", "备注" };
                string[] firstName2 = { "", "", "", "", "", "单价", "合价", "单价", "合价", "", "" };

                //表头宽度
                int[] widths = { 0, 6, 10, 2, 2, 2, 3, 2, 3, 3, 5 };//长度要和标题的长度一样 0代表默认

                //设置表头
                IRow row1 = sheet1.CreateRow(currRow);
                currRow++;
                IRow row2 = sheet1.CreateRow(currRow);
                currRow++;
            
                row1.Height = 600;
                row2.Height = 600;
                for (i1 = 0; i1 < firstName.Length; i1++)
                {
                    row1.CreateCell(i1).SetCellValue(firstName[i1]);
                    row2.CreateCell(i1).SetCellValue(firstName2[i1]);
                    if (widths[i1] == 0)
                    {
                        sheet1.SetColumnWidth(i1, 1000);
                    }
                    else
                    {
                        sheet1.SetColumnWidth(i1, widths[i1] * 1000);
                    }

                }
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 0, 0));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 1, 1));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 2, 2));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 3, 3));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 4, 4));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 2, 5, 6));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 2, 7, 8));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 9, 9));
                sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 10, 10));

                //刷新表头格式
                ICell commCell;
                for (int i = 0; i < 2; i++)
                {
                    for (int j = 0; j < 11; j++)
                    {
                        commCell = sheet1.GetRow(i+ currRow - 2).GetCell(j);
                        //CommCellStyle(book, commCell);
                        commCell.CellStyle = fCellStyle;
                    }
                }


                double taxSum = 0;
                double RgSum = 0;
                double ClSum = 0;
                tempRow = currRow;
                for (int i = 0; i < List1.Count; i++)
                {
                    IRow temprow = sheet1.CreateRow(currRow);
                    currRow++;
                    temprow.Height = 450;
                    temprow.CreateCell(0).SetCellValue(i + 1);
                    temprow.CreateCell(1).SetCellValue(List1[i]["XMMC"].ToString());
                    temprow.CreateCell(2).SetCellValue(List1[i]["GCLJSS"].ToString());
                    temprow.CreateCell(3).SetCellValue(List1[i]["DW"].ToString());
                    temprow.CreateCell(4).SetCellValue(double.Parse(double.Parse(List1[i]["GCL"].ToString()).ToString("F2")));
                    temprow.CreateCell(5).SetCellValue(double.Parse(double.Parse(List1[i]["RGDJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(6).SetCellValue(double.Parse(double.Parse(List1[i]["RGHJ"].ToString()).ToString("F2")));
                    RgSum += double.Parse(List1[i]["RGHJ"].ToString());
                    temprow.CreateCell(7).SetCellValue(double.Parse(double.Parse(List1[i]["CLDJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(8).SetCellValue(double.Parse(double.Parse(List1[i]["CLHJ"].ToString()).ToString("F2")));
                    ClSum += double.Parse(List1[i]["CLHJ"].ToString());
                    temprow.CreateCell(9).SetCellValue(double.Parse(double.Parse(List1[i]["ZJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(10).SetCellValue(List1[i]["Memo"].ToString());
                    taxSum += double.Parse(double.Parse(List1[i]["ZJ"].ToString()).ToString("F2"));
                }
                for (int i = 0; i < List1.Count; i++)
                {
                    for (int j = 1; j < 11; j++)
                    {
                        commCell = sheet1.GetRow(i+ tempRow).GetCell(j);
                        //CommCellStyle(book, commCell);
                        commCell.CellStyle = fCellStyle;
                    }
                }
                //添加税金行
                IRow row3= sheet1.CreateRow(currRow);
                currRow++;
                row3.Height = 450;
                row3.CreateCell(0).SetCellValue(List1.Count + 1);
                row3.CreateCell(1).SetCellValue("税金");
                row3.CreateCell(2).SetCellValue("");
                row3.CreateCell(3).SetCellValue("元");
                row3.CreateCell(4).SetCellValue(double.Parse(taxSum.ToString("F2")));
                row3.CreateCell(5).SetCellValue(double.Parse(Map1["TaxRate"].ToString()).ToString("P"));

                double temptax = double.Parse(Map1["TaxRate"].ToString());            
                row3.CreateCell(6).SetCellValue(double.Parse((taxSum * temptax).ToString("F2")));
                row3.CreateCell(7).SetCellValue("");
                row3.CreateCell(8).SetCellValue("");
                row3.CreateCell(9).SetCellValue(double.Parse((taxSum * temptax).ToString("F2")));
                row3.CreateCell(10).SetCellValue("");
                for (int i = 1; i < 11; i++)
                {
                    commCell = sheet1.GetRow(currRow-1).GetCell(i);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }
                //增加合计
                IRow row4 = sheet1.CreateRow(currRow);
                currRow++;
                row4.Height = 450;
                row4.CreateCell(0).SetCellValue("合计");
                row4.CreateCell(1).SetCellValue("");
                row4.CreateCell(2).SetCellValue("");
                row4.CreateCell(3).SetCellValue("");
                row4.CreateCell(4).SetCellValue("");
                row4.CreateCell(5).SetCellValue("");
                row4.CreateCell(6).SetCellValue(RgSum);
                row4.CreateCell(7).SetCellValue("");
                row4.CreateCell(8).SetCellValue(ClSum);
                row4.CreateCell(9).SetCellValue(double.Parse((RgSum+ClSum+ taxSum * temptax).ToString("F2")));
                row4.CreateCell(10).SetCellValue("");

                sheet1.AddMergedRegion(new CellRangeAddress(currRow-1, currRow-1, 0, 5));
                for (int i = 0; i < 11; i++)
                {
                    commCell = sheet1.GetRow(currRow - 1).GetCell(i);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }

            }
            return book;
        }


        public static string ImportMateAnalysis(string FilePath)
        {
            FileStream fs = null;
            IWorkbook workbook = null;  //new HSSFWorkbook()
            ISheet sheet = null;
            //IRow row = null;
            //ICell cell = null;
            int RowCount = 0;
            int CellCount = 0;
            int? CodeColNum = null; //物料编码所在列
            int? SpecColNum = null; //规格名称所在列
            int? UnitColNum = null; //单位所在列
            int? TypeColNum = null; //类型所在列
            int? RecoColNum = null; //推荐单价所在列
            string LogTime = "";


            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            fs = File.OpenRead(FilePath);     //这里有点问题，需要找到具体的获取文件目录的地方

            if (FilePath.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2007版本excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (FilePath.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2003版本excel
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                //workbook.Close();
                fs.Close();
                return "错误的文件类型";     //这里要返回错误值；
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);  //读取第一个sheet
                }
                else
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";   //这里返回错值
                }

                if (sheet == null)
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";     //这里返回错值
                }
                RowCount = sheet.LastRowNum;
                CellCount = sheet.GetRow(3).LastCellNum;


                //这里判断表格表头内容
                //获取时间
                IRow TempRow1 = sheet.GetRow(0);
                if ("" == TempRow1.GetCell(0).ToString())
                {
                    return "文档生成时间数据错误";
                }
                else
                {
                    LogTime = TempRow1.GetCell(0).ToString();
                    LogTime = LogTime.Substring(0, 10);
                }
                //判断表头
                IRow TempRow2 = sheet.GetRow(3);
                for (int i = 1; i < CellCount; i++)
                {
                    if (TempRow2.GetCell(i).ToString() == "物资品类")
                    {
                        TypeColNum = i;
                    }
                    if (TempRow2.GetCell(i).ToString() == "物料编号")
                    {
                        CodeColNum = i;
                    }
                    if (TempRow2.GetCell(i).ToString() == "物料名称")
                    {
                        SpecColNum = i;
                    }
                    if (TempRow2.GetCell(i).ToString() == "计量单位")
                    {
                        UnitColNum = i;
                    }
                    if (TempRow2.GetCell(i).ToString() == "单价")
                    {
                        RecoColNum = i;
                    }
                }
                if (!TypeColNum.HasValue || !CodeColNum.HasValue || !SpecColNum.HasValue || !UnitColNum.HasValue || !RecoColNum.HasValue)
                {
                    workbook.Close();
                    fs.Close();
                    return "表头有错误";     //表格错误
                }
                double TempDouble = 0;
                for (int i = 4; i < RowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        ICell cell = row.GetCell(RecoColNum.Value);;

                        if (!double.TryParse(cell.ToString(), out TempDouble))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 单价错误";
                        }

                        Dictionary<string, string> map = new Dictionary<string, string>()
                        {
                            ["Code"] = row.GetCell(CodeColNum.Value).ToString(),
                            ["Type"] = row.GetCell(TypeColNum.Value).ToString(),
                            ["Specification"] = row.GetCell(SpecColNum.Value).ToString(),
                            ["Unit"] = row.GetCell(UnitColNum.Value).ToString(),
                            ["RecoPrice"] = row.GetCell(RecoColNum.Value).ToString(),
                            ["IsDelete"] = "0",
                            ["LogTime"]= LogTime
                        };
                        list.Add(map);
                    }
                }
                string sql = "begin ";
                for (int i = 0; i < list.Count; i++)
                {
                    sql += " if exists (select 1 from t_d_Material where Code='" + list[i]["Code"] + "')" +
                        " update t_d_Material set Type='" + list[i]["Type"] + "',Specification='" + list[i]["Specification"] + "'," +
                        " Unit='" + list[i]["Unit"] + "',RecoPrice='" + list[i]["RecoPrice"] + "',LogTime='" + LogTime + "' where Code='" + list[i]["Code"] + "'" +
                        " else ";


                    sql += "insert into t_d_Material (Code,Specification,Type,Unit,RecoPrice,IsDelete,LogTime) values ";
                    sql += "('" + list[i]["Code"] + "','" + list[i]["Specification"] + "','" + list[i]["Type"] + "','"
                        + list[i]["Unit"] + "','" + list[i]["RecoPrice"] + "','0','" + LogTime + "');";
                }
                sql += " end";

                BaseDao.execute(sql);

            }
            catch (Exception e)
            {
                workbook.Close();
                fs.Close();
                return e.Message.ToString().Replace("'", "");
                throw;
            }

            //完了后一定要注意释放流句柄
            workbook.Close();
            fs.Close();




            return "OK";
        }


        public static string ImportEquipFeeMainly(string FilePath,string PreEstId,string PreEstType)
        {
            FileStream fs = null;
            IWorkbook workbook = null;  //new HSSFWorkbook()
            ISheet sheet = null;
            //IRow row = null;
            //ICell cell = null;
            int RowCount = 0;
            int CellCount = 0;
            int? CodeColNum = null; //物料编码所在列
            int? NameColNum = null; //规格名称所在列
            int? UnitColNum = null; //单位所在列
            int? QuantityColNum = null; //数量所在列
            int? SumTaxColNum = null; //含税总价所在列
            string LogTime = "";


            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            fs = File.OpenRead(FilePath);     //这里有点问题，需要找到具体的获取文件目录的地方

            if (FilePath.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2007版本excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (FilePath.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2003版本excel
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                //workbook.Close();
                fs.Close();
                return "错误的文件类型";     //这里要返回错误值；
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);  //读取第一个sheet
                }
                else
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";   //这里返回错值
                }

                if (sheet == null)
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";     //这里返回错值
                }
                RowCount = sheet.LastRowNum;
                CellCount = sheet.GetRow(0).LastCellNum;


                //判断表头
                IRow TempRow = sheet.GetRow(0);
                for (int i = 0; i < CellCount; i++)
                {
                    if (TempRow.GetCell(i).ToString() == "物料编码")
                    {
                        CodeColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "物料名称")
                    {
                        NameColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "单位")
                    {
                        UnitColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "数量")
                    {
                        QuantityColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "含税总价")
                    {
                        SumTaxColNum = i;
                    }
                }
                if (!CodeColNum.HasValue || !NameColNum.HasValue || !UnitColNum.HasValue || !QuantityColNum.HasValue || !SumTaxColNum.HasValue)
                {
                    workbook.Close();
                    fs.Close();
                    return "表头有错误";     //表格错误
                }
                double TempQuantity = 0;
                double tempSumTax = 0;
                for (int i = 1; i <= RowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        ICell cell = row.GetCell(QuantityColNum.Value); 

                        if (!double.TryParse(cell.ToString(), out TempQuantity))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 数量错误或格式不对";
                        }
                        cell = row.GetCell(SumTaxColNum.Value);
                        if (!double.TryParse(cell.ToString(), out tempSumTax))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 含税单价错误或格式不对";
                        }
                        

                        Dictionary<string, string> map = new Dictionary<string, string>()
                        {
                            ["Code"] = row.GetCell(CodeColNum.Value)==null?"": row.GetCell(CodeColNum.Value).ToString(),
                            ["Specification"] = row.GetCell(NameColNum.Value) == null ? "" : row.GetCell(NameColNum.Value).ToString(),
                            ["Unit"] = row.GetCell(UnitColNum.Value) == null ? "" : row.GetCell(UnitColNum.Value).ToString(),
                            ["Quantity"] = TempQuantity.ToString("F2"),
                            ["SumTax"] = tempSumTax.ToString("F2"),
                            ["PreEstId"]= PreEstId,
                            ["PreEstType"]= PreEstType,
                            ["IsDelete"] = "0",
                            
                        };
                        list.Add(map);
                    }
                }
                string sql = "begin ";
                for (int i = 0; i < list.Count; i++)
                {
                    sql += "insert into o_PEEquipFee (Code,Specification,Unit,Quantity,SumTax,PreEstId,PreEstType,IsDelete) values ";
                    sql += "('" + list[i]["Code"] + "','" + list[i]["Specification"] + "','" + list[i]["Unit"] + "','"
                        + list[i]["Quantity"] + "','" + list[i]["SumTax"] + "','"+ list[i]["PreEstId"] + "','"+ list[i]["PreEstType"] + "','0');";
                }
                sql += " end";

                BaseDao.execute(sql);

            }
            catch (Exception e)
            {
                workbook.Close();
                fs.Close();
                return e.Message.ToString().Replace("'", "");
                throw;
            }

            //完了后一定要注意释放流句柄
            workbook.Close();
            fs.Close();




            return "OK";
        }

        public static string ImportMaterialFeeMainly(string FilePath, string PreEstId, string PreEstType)
        {
            FileStream fs = null;
            IWorkbook workbook = null;  //new HSSFWorkbook()
            ISheet sheet = null;
            //IRow row = null;
            //ICell cell = null;
            int RowCount = 0;
            int CellCount = 0;
            int? CodeColNum = null; //物料编码所在列
            int? NameColNum = null; //规格名称所在列
            int? UnitColNum = null; //单位所在列
            int? QuantityColNum = null; //数量所在列
            int? SumTaxColNum = null; //含税总价所在列
            string LogTime = "";


            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>();
            fs = File.OpenRead(FilePath);     //这里有点问题，需要找到具体的获取文件目录的地方

            if (FilePath.IndexOf(".xlsx", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2007版本excel
                workbook = new XSSFWorkbook(fs);
            }
            else if (FilePath.IndexOf(".xls", StringComparison.OrdinalIgnoreCase) > 0)
            {   //2003版本excel
                workbook = new HSSFWorkbook(fs);
            }
            else
            {
                //workbook.Close();
                fs.Close();
                return "错误的文件类型";     //这里要返回错误值；
            }

            try
            {
                if (workbook != null)
                {
                    sheet = workbook.GetSheetAt(0);  //读取第一个sheet
                }
                else
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";   //这里返回错值
                }

                if (sheet == null)
                {
                    //workbook.Close();
                    fs.Close();
                    return "文档内为空";     //这里返回错值
                }
                RowCount = sheet.LastRowNum;
                CellCount = sheet.GetRow(0).LastCellNum;


                //判断表头
                IRow TempRow = sheet.GetRow(0);
                for (int i = 0; i < CellCount; i++)
                {
                    if (TempRow.GetCell(i).ToString() == "物料编码")
                    {
                        CodeColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "物料名称")
                    {
                        NameColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "单位")
                    {
                        UnitColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "数量")
                    {
                        QuantityColNum = i;
                    }
                    if (TempRow.GetCell(i).ToString() == "含税总价")
                    {
                        SumTaxColNum = i;
                    }
                }
                if (!CodeColNum.HasValue || !NameColNum.HasValue || !UnitColNum.HasValue || !QuantityColNum.HasValue || !SumTaxColNum.HasValue)
                {
                    workbook.Close();
                    fs.Close();
                    return "表头有错误";     //表格错误
                }
                double TempQuantity = 0;
                double tempSumTax = 0;
                for (int i = 1; i <= RowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row != null)
                    {
                        ICell cell = row.GetCell(QuantityColNum.Value);

                        if (!double.TryParse(cell.ToString(), out TempQuantity))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 数量错误或格式不对";
                        }
                        cell = row.GetCell(SumTaxColNum.Value);
                        if (!double.TryParse(cell.ToString(), out tempSumTax))
                        {
                            workbook.Close();
                            fs.Close();
                            return "第" + (i + 1).ToString() + "行 含税单价错误或格式不对";
                        }


                        Dictionary<string, string> map = new Dictionary<string, string>()
                        {
                            ["Code"] = row.GetCell(CodeColNum.Value) == null ? "" : row.GetCell(CodeColNum.Value).ToString(),
                            ["Specification"] = row.GetCell(NameColNum.Value) == null ? "" : row.GetCell(NameColNum.Value).ToString(),
                            ["Unit"] = row.GetCell(UnitColNum.Value) == null ? "" : row.GetCell(UnitColNum.Value).ToString(),
                            ["Quantity"] = TempQuantity.ToString("F2"),
                            ["SumTax"] = tempSumTax.ToString("F2"),
                            ["PreEstId"] = PreEstId,
                            ["PreEstType"] = PreEstType,
                            ["IsDelete"] = "0",

                        };
                        list.Add(map);
                    }
                }
                string sql = "begin ";
                for (int i = 0; i < list.Count; i++)
                {
                    sql += "insert into o_PEMaterialFeeUser (Code,Specification,Unit,Quantity,SumTax,PreEstId,PreEstType,IsDelete) values ";
                    sql += "('" + list[i]["Code"] + "','" + list[i]["Specification"] + "','" + list[i]["Unit"] + "','"
                        + list[i]["Quantity"] + "','" + list[i]["SumTax"] + "','" + list[i]["PreEstId"] + "','" + list[i]["PreEstType"] + "','0');";
                }
                sql += " end";

                BaseDao.execute(sql);

            }
            catch (Exception e)
            {
                workbook.Close();
                fs.Close();
                return e.Message.ToString().Replace("'", "");
                throw;
            }

            //完了后一定要注意释放流句柄
            workbook.Close();
            fs.Close();




            return "OK";
        }



        public XSSFWorkbook EstUserExport(string PreEstId,string ProjectName,string Name)
        {

            string temp = "";
            int currRow = 0;
            int tempRow = 0;
            double temp1 = 0;
            int i1 = 0;
            string fileName = "土建核价单 " + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-ff");
            //创建07版Excel文件对象
            XSSFWorkbook book = new XSSFWorkbook();
            ISheet sheet1 = book.CreateSheet("汇总表");
            ISheet sheet2 = book.CreateSheet("安装成本");
            ISheet sheet3 = book.CreateSheet("土建成本");
            ISheet sheet4 = book.CreateSheet("设备成本");
            ISheet sheet5 = book.CreateSheet("材料成本");
            ISheet sheet6 = book.CreateSheet("试验费明细");
            ISheet sheet7 = book.CreateSheet("协调费明细");
            ISheet sheet8 = book.CreateSheet("土建核价单");

            XSSFCellStyle fCellStyle = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = book.CreateDataFormat();
            fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式,#表示空位保留
            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;





            string sql = "";
            ///////////////////////////////////////////////////////////////////
            //汇总表部分      
            sheet1.SetColumnWidth(0, 5000);
            sheet1.SetColumnWidth(1, 5000);
            sheet1.SetColumnWidth(2, 5000);
            sheet1.SetColumnWidth(3, 5000);
            sheet1.SetColumnWidth(4, 5000);
            sheet1.SetColumnWidth(5, 5000);

            var EstimateCtl = DependencyResolver.Current.GetService<EstimateController>();
            var MainlyMap = EstimateCtl.GetPEUserSummary(PreEstId, "用户工程");
            sql = "select * from o_PreEstUser where PreEstId=" + PreEstId;
            Dictionary<string, object> map2 = new Dictionary<string, object>();
            string[] field = new string[] { "XMMC", "YDXZ", "SSQY", "JZMJ", "RL", "WXCD", "HXNBJJE", "Memo_WXTJ","Memo_WXSB","Memo_WXCL","Memo_KBSSB","Memo_KBSCL","Memo_GYSB","Memo_GYCL",
                "Memo_ZYSB", "Memo_ZYCL", "Memo_ZTAZ", "Memo_ZTTJSGF", "Memo_ZTSYTSF", "YZYQ_CF", "Memo_YZYQCF", "YZYQ_YHYB","Memo_YZYQYHYB","YZYQ_LLLJ","Memo_YZYQLLLJ","YZYQ_CDZ","Memo_YZYQCDZ","DJHTJE","Memo_DJHTJE",
                    "SJF","Memo_SJF","JLF","Memo_JLF","DJGLF","Memo_DJGLF","SJ","Memo_SJ","ZJCB","Memo_ZJCB","GSGLF","Memo_GSGLF","Memo_YWF","LRL","Memo_LRL" };
            map2 = dao.GetList(sql, field)[0];
            MainlyMap = MainlyMap.Concat(map2).ToDictionary(k => k.Key, v => v.Value);

            //以下操作把数值为空的项目变为0
            var tempmap = new Dictionary<string, object>();            
            foreach (string key  in MainlyMap.Keys)
            {
                tempmap.Add(key, MainlyMap[key]);
            }
            foreach (string key in tempmap.Keys)
            {
                if (!key.Contains("Memo"))
                {
                    if (tempmap[key].ToString() == "")
                    {
                        MainlyMap[key] = 0;
                    }
                }

            }

            IRow row0 = sheet1.CreateRow(currRow);   //创建行
            IRow rowSum;
            currRow++;
            ICell cell = row0.CreateCell(0);                     //创建列

            temp = ProjectName + "——" + Name + "(报价测算表)";
            cell.SetCellValue(temp);
            setCellStyle(book, cell);
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            //第2行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("项目名称");
            row0.CreateCell(1).SetCellValue(MainlyMap["XMMC"].ToString());
            row0.CreateCell(2).SetCellValue("用电性质");
            row0.CreateCell(3).SetCellValue(MainlyMap["YDXZ"].ToString());
            row0.CreateCell(4).SetCellValue("所属区域");
            row0.CreateCell(5).SetCellValue(MainlyMap["SSQY"].ToString());
            //第3行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("建筑面积");
            row0.CreateCell(1).SetCellValue(MainlyMap["JZMJ"].ToString());
            row0.CreateCell(2).SetCellValue("容量(KVA)");
            row0.CreateCell(3).SetCellValue(MainlyMap["RL"].ToString());
            row0.CreateCell(4).SetCellValue("外线长度");
            row0.CreateCell(5).SetCellValue(MainlyMap["WXCD"].ToString());
            //第4行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("红线内报价");
            row0.CreateCell(1).SetCellValue(double.Parse(MainlyMap["HXNBJJE"].ToString()));
            row0.CreateCell(2).SetCellValue("红线内单方造价");
            row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["HXNBJJE"].ToString())/ double.Parse(MainlyMap["JZMJ"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue("红线内容量单价");
            row0.CreateCell(5).SetCellValue((double.Parse(MainlyMap["HXNBJJE"].ToString()) / double.Parse(MainlyMap["RL"].ToString())).ToString("F2"));
            //第5行
            rowSum = sheet1.CreateRow(currRow);
            currRow++;
            //row0.CreateCell(0).SetCellValue("总报价");
            //row0.CreateCell(1).SetCellValue(double.Parse(MainlyMap["HXNBJJE"].ToString()));
            //row0.CreateCell(2).SetCellValue("单方造价");
            //row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["HXNBJJE"].ToString()) / double.Parse(MainlyMap["JZMJ"].ToString())).ToString("F2"));
            //row0.CreateCell(4).SetCellValue("容量单价");
            //row0.CreateCell(5).SetCellValue((double.Parse(MainlyMap["HXNBJJE"].ToString()) / double.Parse(MainlyMap["RL"].ToString())).ToString("F2"));

            //第6行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("费用类型");
            row0.CreateCell(1).SetCellValue("费用类别");
            row0.CreateCell(2).SetCellValue("费用目录");
            row0.CreateCell(3).SetCellValue("成本测算");
            row0.CreateCell(4).SetCellValue("备注");
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));

            //第7行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("外线");
            row0.CreateCell(2).SetCellValue("外线土建施工费");
            row0.CreateCell(3).SetCellValue(MainlyMap["CivilPrice_WX"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_WXTJ"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第8行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("外线");
            row0.CreateCell(2).SetCellValue("外线设备费");
            row0.CreateCell(3).SetCellValue(MainlyMap["EquipFee_WX"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_WXSB"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第9行
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("外线");
            row0.CreateCell(2).SetCellValue("外线材料费");
            row0.CreateCell(3).SetCellValue(MainlyMap["MeterialFee_WX"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_WXCL"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //开闭所
            //第10行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("开闭所");
            row0.CreateCell(2).SetCellValue("设备费");
            row0.CreateCell(3).SetCellValue(MainlyMap["EquipFee_KBS"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_KBSSB"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第11行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("开闭所");
            row0.CreateCell(2).SetCellValue("材料费");
            row0.CreateCell(3).SetCellValue(MainlyMap["MeterialFee_KBS"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_KBSCL"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //公配
            //第12行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("公配");
            row0.CreateCell(2).SetCellValue("设备费");
            row0.CreateCell(3).SetCellValue(MainlyMap["EquipFee_GP"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_GYSB"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第13行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("公配");
            row0.CreateCell(2).SetCellValue("材料费");
            row0.CreateCell(3).SetCellValue(MainlyMap["MeterialFee_GP"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_GYCL"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //专配
            //第14行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("专配");
            row0.CreateCell(2).SetCellValue("设备费");
            row0.CreateCell(3).SetCellValue(MainlyMap["EquipFee_ZP"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_ZYSB"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第15行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("专配");
            row0.CreateCell(2).SetCellValue("材料费");
            row0.CreateCell(3).SetCellValue(MainlyMap["MeterialFee_ZP"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_ZYCL"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //总体
            //第16行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("总体");
            row0.CreateCell(2).SetCellValue("安装施工费");
            temp1 = double.Parse(MainlyMap["InstallFee_KBS"].ToString()) + double.Parse(MainlyMap["InstallFee_GP"].ToString()) +
                double.Parse(MainlyMap["InstallFee_ZP"].ToString())+ double.Parse(MainlyMap["InstallFee_WX"].ToString());
            row0.CreateCell(3).SetCellValue(temp1.ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_ZTAZ"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第17行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("总体");
            row0.CreateCell(2).SetCellValue("土建施工费(红线内)");
            row0.CreateCell(3).SetCellValue(MainlyMap["CivilPrice_HXN"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_ZTTJSGF"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第18行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("总体");
            row0.CreateCell(2).SetCellValue("实验调试费");
            row0.CreateCell(3).SetCellValue(MainlyMap["TestFee"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_ZTSYTSF"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //业主要求
            //第19行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("柴发");
            row0.CreateCell(3).SetCellValue(MainlyMap["YZYQ_CF"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_YZYQCF"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第20行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("一户一表费");
            row0.CreateCell(3).SetCellValue(MainlyMap["YZYQ_YHYB"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_YZYQYHYB"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第21行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("联络路径");
            row0.CreateCell(3).SetCellValue(MainlyMap["YZYQ_LLLJ"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_YZYQLLLJ"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //第22行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程直接成本");
            row0.CreateCell(1).SetCellValue("业主要求");
            row0.CreateCell(2).SetCellValue("充电桩");
            row0.CreateCell(3).SetCellValue(MainlyMap["YZYQ_CDZ"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_YZYQCDZ"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            //小计
            //第23行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            temp1 = double.Parse(MainlyMap["YZYQ_CDZ"].ToString()) + double.Parse(MainlyMap["YZYQ_LLLJ"].ToString()) +
                double.Parse(MainlyMap["YZYQ_YHYB"].ToString()) + double.Parse(MainlyMap["YZYQ_CF"].ToString()) +
                double.Parse(MainlyMap["TestFee"].ToString()) + double.Parse(MainlyMap["CivilPrice_HXN"].ToString()) +
                double.Parse(MainlyMap["InstallFee_WX"].ToString()) + double.Parse(MainlyMap["InstallFee_KBS"].ToString()) +
                double.Parse(MainlyMap["InstallFee_GP"].ToString()) + double.Parse(MainlyMap["InstallFee_ZP"].ToString()) +
                double.Parse(MainlyMap["MeterialFee_ZP"].ToString()) + double.Parse(MainlyMap["EquipFee_ZP"].ToString()) +
                double.Parse(MainlyMap["MeterialFee_GP"].ToString()) + double.Parse(MainlyMap["EquipFee_GP"].ToString())+
                double.Parse(MainlyMap["MeterialFee_KBS"].ToString()) + double.Parse(MainlyMap["EquipFee_KBS"].ToString()) +
                double.Parse(MainlyMap["MeterialFee_WX"].ToString()) + double.Parse(MainlyMap["EquipFee_WX"].ToString()) +
                double.Parse(MainlyMap["CivilPrice_WX"].ToString());
            row0.CreateCell(3).SetCellValue(temp1.ToString("F2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 3, 5));


            //合并工程直接成本单元格
            sheet1.AddMergedRegion(new CellRangeAddress(6, 6+15, 0, 0));
            sheet1.AddMergedRegion(new CellRangeAddress(6, 8, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(9, 10, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(11, 12, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(13, 14, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(15, 17, 1, 1));
            sheet1.AddMergedRegion(new CellRangeAddress(18, 21, 1, 1));

            //第24行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("多径合同金额(元)");
            row0.CreateCell(2).SetCellValue(MainlyMap["DJHTJE"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_DJHTJE"].ToString());
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 2, 3));
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            temp1 += double.Parse(MainlyMap["DJHTJE"].ToString());

            //第25行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("电力局费用");
            row0.CreateCell(2).SetCellValue("设计费");
            row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["SJF"].ToString())* double.Parse(MainlyMap["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["SJF"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_SJF"].ToString());
            temp1 += double.Parse(MainlyMap["SJF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString());


            //第26行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("电力局费用");
            row0.CreateCell(2).SetCellValue("监理费");
            row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["JLF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["JLF"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_JLF"].ToString());
            temp1 += double.Parse(MainlyMap["JLF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString());

            //第27行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("电力局费用");
            row0.CreateCell(2).SetCellValue("多径管理费");
            row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["DJGLF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["DJGLF"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_DJGLF"].ToString());
            temp1 += double.Parse(MainlyMap["DJGLF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString());

            //第28行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("税金");
            row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["SJ"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["SJ"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_SJ"].ToString());
            temp1 += double.Parse(MainlyMap["SJ"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString());

            //第29行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("资金成本");
            row0.CreateCell(3).SetCellValue(MainlyMap["ZJCB"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["ZJCB"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_ZJCB"].ToString());
            temp1 += double.Parse(MainlyMap["ZJCB"].ToString());

            //第30行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("公司管理费");
            row0.CreateCell(3).SetCellValue((double.Parse(MainlyMap["GSGLF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["GSGLF"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_GSGLF"].ToString());
            temp1 += double.Parse(MainlyMap["GSGLF"].ToString()) * double.Parse(MainlyMap["DJHTJE"].ToString());

            //第31行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("工程成本管理");
            row0.CreateCell(1).SetCellValue("公司费用");
            row0.CreateCell(2).SetCellValue("业务费");
            row0.CreateCell(3).SetCellValue(MainlyMap["CoordFee"].ToString());
            row0.CreateCell(4).SetCellValue(MainlyMap["Memo_YWF"].ToString());
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 4, 5));
            temp1 += double.Parse(MainlyMap["CoordFee"].ToString());

            //合计、利润、报价金额
            //第32行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(3).SetCellValue(temp1.ToString("F2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 3, 5));


            //第33行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("利润");
            row0.CreateCell(3).SetCellValue((temp1 * double.Parse(MainlyMap["LRL"].ToString())).ToString("F2"));
            row0.CreateCell(4).SetCellValue(MainlyMap["LRL"].ToString());
            row0.CreateCell(5).SetCellValue(MainlyMap["Memo_LRL"].ToString());
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));


            //第34行数据整理
            row0 = sheet1.CreateRow(currRow);
            currRow++;
            row0.CreateCell(0).SetCellValue("报价金额");
            row0.CreateCell(3).SetCellValue((temp1 * double.Parse(MainlyMap["LRL"].ToString())+temp1).ToString("F2"));
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));
            sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 3, 5));


            //第5行数据
            rowSum.CreateCell(0).SetCellValue("总报价");
            rowSum.CreateCell(1).SetCellValue((temp1 * double.Parse(MainlyMap["LRL"].ToString()) + temp1).ToString("F2"));
            rowSum.CreateCell(2).SetCellValue("单方造价");
            rowSum.CreateCell(3).SetCellValue(((temp1 * double.Parse(MainlyMap["LRL"].ToString()) + temp1)/ double.Parse(MainlyMap["JZMJ"].ToString())).ToString("F2"));
            rowSum.CreateCell(4).SetCellValue("容量单价");
            rowSum.CreateCell(5).SetCellValue(((temp1 * double.Parse(MainlyMap["LRL"].ToString()) + temp1) / double.Parse(MainlyMap["RL"].ToString())).ToString("F2"));


            //合并工程直接成本单元格
            sheet1.AddMergedRegion(new CellRangeAddress(23, 30, 0, 0));

            //刷新表头格式
            ICell commCell;
            for (int i = 1; i < 34; i++)
            {
                for (int j = 0; j < 6; j++)
                {
                    commCell = sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }
            }

            sheet2 = EstUserInstallSheet(PreEstId, sheet2, fCellStyle);
            sheet3 = EstUserCivilSheet(PreEstId, sheet3, fCellStyle);
            sheet4 = EstUserEquipSheet(PreEstId, sheet4, fCellStyle);
            sheet5 = EstUserMaterialSheet(PreEstId, sheet5, fCellStyle);
            sheet6 = EstUserTestSheet(PreEstId, sheet6, fCellStyle);
            sheet7 = EstUserCoordFeeSheet(PreEstId, sheet7, fCellStyle);
            sheet8 = EstUserCivilSheetDetail(PreEstId, sheet8, fCellStyle,book);

            return book;
        }


        public ISheet EstUserInstallSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
             sql= "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
                "FROM o_PEInstallFee where TabType='WX' and IsDelete<>1 and PreEstId="+PreEstId+" and PreEstType='用户工程' ORDER BY Specification";
            var ListWX = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo","TabType" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //KBS
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='KBS' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY Specification";
            var ListKBS = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListKBS.Count; i++)
            {
                if (ListKBS[i]["Sum"].ToString() == "")
                {
                    ListKBS[i]["Sum"] = 0;
                }
            }
            //GP
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='GP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY Specification";
            var ListGP = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListGP.Count; i++)
            {
                if (ListGP[i]["Sum"].ToString() == "")
                {
                    ListGP[i]["Sum"] = 0;
                }
            }
            //ZP
            sql = "SELECT Name,Specification,Content,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo,TabType " +
               "FROM o_PEInstallFee where TabType='ZP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY Specification";
            var ListZP = dao.GetList(sql, new string[] { "Name", "Specification", "Content", "Unit", "Quantity", "QuoteMoney", "Sum", "Memo", "TabType" });
            //清零操作
            for (int i = 0; i < ListZP.Count; i++)
            {
                if (ListZP[i]["Sum"].ToString() == "")
                {
                    ListZP[i]["Sum"] = 0;
                }
            }
            XSSFWorkbook book = new XSSFWorkbook();
            XSSFCellStyle fCellStyle2 = (XSSFCellStyle)book.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)book.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle2.SetFont(ffont);
            fCellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐


            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 8000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 2000);
            Sheet1.SetColumnWidth(4, 2000);
            Sheet1.SetColumnWidth(5, 3000);
            Sheet1.SetColumnWidth(6, 4000);
            Sheet1.SetColumnWidth(7, 5000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("项目名称");
            row0.CreateCell(1).SetCellValue("规格型号");
            row0.CreateCell(2).SetCellValue("主要工作内容");
            row0.CreateCell(3).SetCellValue("单位");
            row0.CreateCell(4).SetCellValue("工程量");
            row0.CreateCell(5).SetCellValue("单价");
            row0.CreateCell(6).SetCellValue("合价");
            row0.CreateCell(7).SetCellValue("备注");

            ////////////////////////////////////////////////////
            //外线循环
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListWX.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListWX[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListWX[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            ////////////////////////////////////////////////////
            //开闭所循环
            tempSum = 0;
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("开闭所部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListKBS.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListKBS[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListKBS[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListKBS[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListKBS[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListKBS[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListKBS[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListKBS[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListKBS[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListKBS[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            ////////////////////////////////////////////////////
            //公配循环
            tempSum = 0;
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("公配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListGP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListGP[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListGP[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListGP[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListGP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListGP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListGP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListGP[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListGP[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListGP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            ////////////////////////////////////////////////////
            //专配循环
            tempSum = 0;
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("专配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

            for (int i = 0; i < ListZP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListZP[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListZP[i]["Specification"].ToString());
                row0.CreateCell(2).SetCellValue(ListZP[i]["Content"].ToString());
                row0.CreateCell(3).SetCellValue(ListZP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListZP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListZP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListZP[i]["Sum"].ToString());
                row0.CreateCell(7).SetCellValue(ListZP[i]["Memo"].ToString());
                //Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 7));

                tempSum += double.Parse(ListZP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;

            //总计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("总计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(Sum.ToString("F2"));
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));

            //刷新各行格式
            ICell commCell;
            for (int i = 1; i < currRow-1; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 8; j++)
                {
                    
                    commCell = Sheet1.GetRow(i).GetCell(j);

                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                    
                }
            }




            return Sheet1;
        }

        public ISheet EstUserEquipSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListWX = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //KBS
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='KBS' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListKBS = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListKBS.Count; i++)
            {
                if (ListKBS[i]["Sum"].ToString() == "")
                {
                    ListKBS[i]["Sum"] = 0;
                }
            }
            //GP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='GP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListGP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListGP.Count; i++)
            {
                if (ListGP[i]["Sum"].ToString() == "")
                {
                    ListGP[i]["Sum"] = 0;
                }
            }
            //ZP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEEquipFee where TabType='ZP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListZP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListZP.Count; i++)
            {
                if (ListZP[i]["Sum"].ToString() == "")
                {
                    ListZP[i]["Sum"] = 0;
                }
            }

            Sheet1.SetColumnWidth(0, 3000);
            Sheet1.SetColumnWidth(1, 4000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 2000);
            Sheet1.SetColumnWidth(4, 2000);
            Sheet1.SetColumnWidth(5, 3000);
            Sheet1.SetColumnWidth(6, 3000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("编号");
            row0.CreateCell(1).SetCellValue("名称");
            row0.CreateCell(2).SetCellValue("规格型号");
            row0.CreateCell(3).SetCellValue("单位");
            row0.CreateCell(4).SetCellValue("数量");
            row0.CreateCell(5).SetCellValue("含税单价");
            row0.CreateCell(6).SetCellValue("含税合价");


            ////////////////////////////////////////////////////
            //外线循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListWX.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListWX[i]["Sum"].ToString());
                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //开闭所循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("开闭所部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListKBS.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListKBS[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListKBS[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListKBS[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListKBS[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListKBS[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListKBS[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListKBS[i]["Sum"].ToString());
                tempSum += double.Parse(ListKBS[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //公配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("公配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListGP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListGP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListGP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListGP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListGP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListGP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListGP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListGP[i]["Sum"].ToString());
                tempSum += double.Parse(ListGP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //专配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("专配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListZP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListZP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListZP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListZP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListZP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListZP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListZP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListZP[i]["Sum"].ToString());
                tempSum += double.Parse(ListZP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;

            //合计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(Sum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 7; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }


            return Sheet1;
        }

        public ISheet EstUserMaterialSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListWX = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //KBS
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='KBS' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListKBS = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListKBS.Count; i++)
            {
                if (ListKBS[i]["Sum"].ToString() == "")
                {
                    ListKBS[i]["Sum"] = 0;
                }
            }
            //GP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='GP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListGP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListGP.Count; i++)
            {
                if (ListGP[i]["Sum"].ToString() == "")
                {
                    ListGP[i]["Sum"] = 0;
                }
            }
            //ZP
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='ZP' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListZP = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListZP.Count; i++)
            {
                if (ListZP[i]["Sum"].ToString() == "")
                {
                    ListZP[i]["Sum"] = 0;
                }
            }
            //YXZB
            sql = "SELECT Code,EquipName,Specification,Unit,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum " +
               "FROM o_PEMaterialFeeUser where TabType='YXZB' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY EquipName";
            var ListYXZB = dao.GetList(sql, new string[] { "Code", "EquipName", "Specification", "Unit", "Quantity", "QuoteMoney", "Sum" });
            //清零操作
            for (int i = 0; i < ListYXZB.Count; i++)
            {
                if (ListYXZB[i]["Sum"].ToString() == "")
                {
                    ListYXZB[i]["Sum"] = 0;
                }
            }
            Sheet1.SetColumnWidth(0, 3000);
            Sheet1.SetColumnWidth(1, 4000);
            Sheet1.SetColumnWidth(2, 5000);
            Sheet1.SetColumnWidth(3, 2000);
            Sheet1.SetColumnWidth(4, 2000);
            Sheet1.SetColumnWidth(5, 3000);
            Sheet1.SetColumnWidth(6, 3000);

            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("编号");
            row0.CreateCell(1).SetCellValue("名称");
            row0.CreateCell(2).SetCellValue("规格型号");
            row0.CreateCell(3).SetCellValue("单位");
            row0.CreateCell(4).SetCellValue("数量");
            row0.CreateCell(5).SetCellValue("含税单价");
            row0.CreateCell(6).SetCellValue("含税合价");

            ////////////////////////////////////////////////////
            //外线循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListWX.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListWX[i]["Sum"].ToString());
                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //开闭所循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("开闭所部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListKBS.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListKBS[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListKBS[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListKBS[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListKBS[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListKBS[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListKBS[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListKBS[i]["Sum"].ToString());
                tempSum += double.Parse(ListKBS[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //公配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("公配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListGP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListGP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListGP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListGP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListGP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListGP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListGP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListGP[i]["Sum"].ToString());
                tempSum += double.Parse(ListGP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;
            ////////////////////////////////////////////////////
            //专配循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("专配部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListZP.Count; i++)
            {

                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListZP[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListZP[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListZP[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListZP[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListZP[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListZP[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListZP[i]["Sum"].ToString());
                tempSum += double.Parse(ListZP[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;

            ////////////////////////////////////////////////////
            //运行准备循环
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 20 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("运行准备部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue("");
            row0.CreateCell(7).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 6));

            for (int i = 0; i < ListYXZB.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListYXZB[i]["Code"].ToString());
                row0.CreateCell(1).SetCellValue(ListYXZB[i]["EquipName"].ToString());
                row0.CreateCell(2).SetCellValue(ListYXZB[i]["Specification"].ToString());
                row0.CreateCell(3).SetCellValue(ListYXZB[i]["Unit"].ToString());
                row0.CreateCell(4).SetCellValue(ListYXZB[i]["Quantity"].ToString());
                row0.CreateCell(5).SetCellValue(ListYXZB[i]["QuoteMoney"].ToString());
                row0.CreateCell(6).SetCellValue(ListYXZB[i]["Sum"].ToString());
                tempSum += double.Parse(ListYXZB[i]["Sum"].ToString());
            }
            //小计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("小计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(tempSum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            Sum += tempSum;
            tempSum = 0;


            //合计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            row0.CreateCell(6).SetCellValue(Sum.ToString("F2"));
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 7; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;
        }


        public ISheet EstUserTestSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //YXZB
            sql = "SELECT TestName,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo " +
               "FROM o_PETestFee where IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY TestName";
            var ListTest = dao.GetList(sql, new string[] { "TestName", "Quantity", "QuoteMoney", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListTest.Count; i++)
            {
                if (ListTest[i]["Sum"].ToString() == "")
                {
                    ListTest[i]["Sum"] = 0;
                }
            }
            Sheet1.SetColumnWidth(0, 5000);
            Sheet1.SetColumnWidth(1, 3000);
            Sheet1.SetColumnWidth(2, 3000);
            Sheet1.SetColumnWidth(3, 4000);
            Sheet1.SetColumnWidth(4, 5000);


            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            bool tagTitle = true;//表面建立表头
            IRow rowSum;
            string Type = "";
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("试验费名称");
            row0.CreateCell(1).SetCellValue("工程量");
            row0.CreateCell(2).SetCellValue("单价");
            row0.CreateCell(3).SetCellValue("合价");
            row0.CreateCell(4).SetCellValue("备注");
            row0.Height = 20 * 20;

            ////////////////////////////////////////////////////
            for (int i = 0; i < ListTest.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListTest[i]["TestName"].ToString());
                row0.CreateCell(1).SetCellValue(ListTest[i]["Quantity"].ToString());
                row0.CreateCell(2).SetCellValue(ListTest[i]["QuoteMoney"].ToString());
                row0.CreateCell(3).SetCellValue(ListTest[i]["Sum"].ToString());
                row0.CreateCell(4).SetCellValue(ListTest[i]["Memo"].ToString());
                tempSum += double.Parse(ListTest[i]["Sum"].ToString());
            }
            //合计计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(4).SetCellValue(""); 
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));


            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 5; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;
        }

        public ISheet EstUserCoordFeeSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //YXZB
            sql = "SELECT CooContent,CooDepart,Quantity,QuoteMoney,Quantity*QuoteMoney as Sum,Memo " +
               "FROM o_PECoordFee where IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY CooContent";
            var ListCoordFee = dao.GetList(sql, new string[] { "CooContent", "CooDepart", "Quantity", "QuoteMoney", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListCoordFee.Count; i++)
            {
                if (ListCoordFee[i]["Sum"].ToString() == "")
                {
                    ListCoordFee[i]["Sum"] = 0;
                }
            }
            Sheet1.SetColumnWidth(0, 4000);
            Sheet1.SetColumnWidth(1, 6000);
            Sheet1.SetColumnWidth(2, 3000);
            Sheet1.SetColumnWidth(3, 3000);
            Sheet1.SetColumnWidth(4, 3000);
            Sheet1.SetColumnWidth(5, 5000);


            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("协调内容");
            row0.CreateCell(1).SetCellValue("协调部门");
            row0.CreateCell(2).SetCellValue("数量");
            row0.CreateCell(3).SetCellValue("单价");
            row0.CreateCell(4).SetCellValue("合价");
            row0.CreateCell(5).SetCellValue("备注");
            row0.Height = 20 * 20;

            ////////////////////////////////////////////////////
            for (int i = 0; i < ListCoordFee.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListCoordFee[i]["CooContent"].ToString());
                row0.CreateCell(1).SetCellValue(ListCoordFee[i]["CooDepart"].ToString());
                row0.CreateCell(2).SetCellValue(ListCoordFee[i]["Quantity"].ToString());
                row0.CreateCell(3).SetCellValue(ListCoordFee[i]["QuoteMoney"].ToString());
                row0.CreateCell(4).SetCellValue(ListCoordFee[i]["Sum"].ToString());
                row0.CreateCell(5).SetCellValue(ListCoordFee[i]["Memo"].ToString());
                tempSum += double.Parse(ListCoordFee[i]["Sum"].ToString());
            }
            //合计计
            row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("合计");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 2));


            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 6; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }

            return Sheet1;
        }


        public ISheet EstUserCivilSheet(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle)
        {
            string sql = "";
            //WX
            sql = "SELECT Name,Unit,UnitPrice,Quantity,Quantity*UnitPrice as Sum,Memo " +
               "FROM o_PECivilPrice where TabType='WX' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY PECivilPriceId desc";
            var ListWX = dao.GetList(sql, new string[] { "Name", "Unit", "UnitPrice", "Quantity", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListWX.Count; i++)
            {
                if (ListWX[i]["Sum"].ToString() == "")
                {
                    ListWX[i]["Sum"] = 0;
                }
            }
            //HXN
            sql = "SELECT Name,Unit,UnitPrice,Quantity,Quantity*UnitPrice as Sum,Memo " +
               "FROM o_PECivilPrice where TabType='HXN' and IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程' ORDER BY PECivilPriceId desc";
            var ListHXN = dao.GetList(sql, new string[] { "Name", "Unit", "UnitPrice", "Quantity", "Sum", "Memo" });
            //清零操作
            for (int i = 0; i < ListHXN.Count; i++)
            {
                if (ListHXN[i]["Sum"].ToString() == "")
                {
                    ListHXN[i]["Sum"] = 0;
                }
            }


            Sheet1.SetColumnWidth(0, 6000);
            Sheet1.SetColumnWidth(1, 2000);
            Sheet1.SetColumnWidth(2, 2000);
            Sheet1.SetColumnWidth(3, 3000);
            Sheet1.SetColumnWidth(4, 3000);
            Sheet1.SetColumnWidth(5, 5000);


            double Sum = 0;
            double tempSum = 0;
            int currRow = 0;
            //设置表头
            IRow row0 = Sheet1.CreateRow(currRow);   //创建行          
            currRow++;
            row0.CreateCell(0).SetCellValue("项目名称");
            row0.CreateCell(1).SetCellValue("单位");
            row0.CreateCell(2).SetCellValue("工程量");
            row0.CreateCell(3).SetCellValue("单价");
            row0.CreateCell(4).SetCellValue("合价");
            row0.CreateCell(5).SetCellValue("备注");
            row0.Height = 20 * 20;
            /////////////////////////////////////////////////////////////////////
            //WX
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 25 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));

            for (int i = 0; i < ListWX.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListWX[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListWX[i]["Unit"].ToString());
                row0.CreateCell(2).SetCellValue(ListWX[i]["Quantity"].ToString());
                row0.CreateCell(3).SetCellValue(ListWX[i]["UnitPrice"].ToString());
                row0.CreateCell(4).SetCellValue(ListWX[i]["Sum"].ToString());
                row0.CreateCell(5).SetCellValue(ListWX[i]["Memo"].ToString());
                tempSum += double.Parse(ListWX[i]["Sum"].ToString());
            }
            /////////////////////////////////////////////////////////////////////
            //HXN
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 25 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue("");
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));

            for (int i = 0; i < ListHXN.Count; i++)
            {
                row0 = Sheet1.CreateRow(currRow);   //创建行          
                currRow++;
                row0.CreateCell(0).SetCellValue(ListHXN[i]["Name"].ToString());
                row0.CreateCell(1).SetCellValue(ListHXN[i]["Unit"].ToString());
                row0.CreateCell(2).SetCellValue(ListHXN[i]["Quantity"].ToString());
                row0.CreateCell(3).SetCellValue(ListHXN[i]["UnitPrice"].ToString());
                row0.CreateCell(4).SetCellValue(ListHXN[i]["Sum"].ToString());
                row0.CreateCell(5).SetCellValue(ListHXN[i]["Memo"].ToString());
                tempSum += double.Parse(ListHXN[i]["Sum"].ToString());
            }

            ///////////////////////////////////////////////////////////////////////////////
            //合计
            row0 = Sheet1.CreateRow(currRow);   //创建行   
            row0.Height = 25 * 20;
            currRow++;
            row0.CreateCell(0).SetCellValue("外线部分");
            row0.CreateCell(1).SetCellValue("");
            row0.CreateCell(2).SetCellValue("");
            row0.CreateCell(3).SetCellValue("");
            row0.CreateCell(4).SetCellValue(tempSum.ToString("F2"));
            row0.CreateCell(5).SetCellValue("");
            Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 3));

            //刷新各行格式
            ICell commCell;
            for (int i = 0; i < currRow; i++)
            {
                //Sheet1.GetRow(i).Height = 60 * 20;
                for (int j = 0; j < 6; j++)
                {
                    commCell = Sheet1.GetRow(i).GetCell(j);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;

                }
            }





            return Sheet1;



        }


        public ISheet EstUserCivilSheetDetail(string PreEstId, ISheet Sheet1, XSSFCellStyle fCellStyle, XSSFWorkbook workbook)
        {
            string sql = "";
            string temp = "";
            int i1 = 0;
            int currRow = 0;
            int tempRow = 0;

            
            XSSFCellStyle fCellStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle2.SetFont(ffont);
            fCellStyle2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            

            //下面先获取PECivilPriceId
            sql = "select PECivilPriceId from o_PECivilPrice where IsDelete<>1 and PreEstId=" + PreEstId + " and PreEstType='用户工程'";

            var PECivilIdList = dao.GetList(sql, new string[] { "PECivilPriceId"});

            for (int ii = 0; ii < PECivilIdList.Count; ii++)
            {

                sql = "select * from o_PECivilPrice where PECivilPriceId=" + PECivilIdList[ii]["PECivilPriceId"].ToString();
                var Map1 = dao.GetList(sql, new string[] { "PECivilPriceId", "Name", "TaxRate", "Unit", "UnitPrice", "Memo" })[0];
                sql = "select XMMC,GCLJSS,DW,GCL,RGDJ,GCL*RGDJ as RGHJ,CLDJ,GCL*CLDJ as CLHJ,GCL*(RGDJ+CLDJ) AS ZJ,Memo from o_PECivilPriceSingle where PECivilPriceId=" + PECivilIdList[ii]["PECivilPriceId"].ToString() + " order by id asc";
                var List1 = dao.GetList(sql, new string[] { "XMMC", "GCLJSS", "DW", "GCL", "RGDJ", "RGHJ", "CLDJ", "CLHJ", "ZJ", "Memo" });

                IRow row0 = Sheet1.CreateRow(currRow);   //创建行
                currRow++;
                ICell cell = row0.CreateCell(0);                     //创建列

                temp = Map1["Name"].ToString();
                cell.SetCellValue(temp);
                cell.CellStyle = fCellStyle2;
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 10));
                //创建表头
                string[] firstName = { "序号", "项目名称", "工程量计算式", "单位", "工程量", "人工费", "", "材料费", "", "总价", "备注" };
                string[] firstName2 = { "", "", "", "", "", "单价", "合价", "单价", "合价", "", "" };

                //表头宽度
                int[] widths = { 0, 6, 10, 2, 2, 2, 3, 2, 3, 3, 5 };//长度要和标题的长度一样 0代表默认

                //设置表头
                IRow row1 = Sheet1.CreateRow(currRow);
                currRow++;
                IRow row2 = Sheet1.CreateRow(currRow);
                currRow++;

                row1.Height = 600;
                row2.Height = 600;
                for (i1 = 0; i1 < firstName.Length; i1++)
                {
                    row1.CreateCell(i1).SetCellValue(firstName[i1]);
                    row2.CreateCell(i1).SetCellValue(firstName2[i1]);
                    if (widths[i1] == 0)
                    {
                        Sheet1.SetColumnWidth(i1, 1000);
                    }
                    else
                    {
                        Sheet1.SetColumnWidth(i1, widths[i1] * 1000);
                    }

                }
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 0, 0));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 1, 1));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 2, 2));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 3, 3));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 4, 4));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 2, 5, 6));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 2, 7, 8));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 9, 9));
                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 2, currRow - 1, 10, 10));

                //刷新表头格式
                ICell commCell;
                for (int i = 0; i < 2; i++)
                {
                    for (int j = 0; j < 11; j++)
                    {
                        commCell = Sheet1.GetRow(i + currRow - 2).GetCell(j);
                        //CommCellStyle(book, commCell);
                        commCell.CellStyle = fCellStyle;
                    }
                }


                double taxSum = 0;
                double RgSum = 0;
                double ClSum = 0;
                tempRow = currRow;
                for (int i = 0; i < List1.Count; i++)
                {
                    IRow temprow = Sheet1.CreateRow(currRow);
                    currRow++;
                    temprow.Height = 450;
                    temprow.CreateCell(0).SetCellValue(i + 1);
                    temprow.CreateCell(1).SetCellValue(List1[i]["XMMC"].ToString());
                    temprow.CreateCell(2).SetCellValue(List1[i]["GCLJSS"].ToString());
                    temprow.CreateCell(3).SetCellValue(List1[i]["DW"].ToString());
                    temprow.CreateCell(4).SetCellValue(double.Parse(double.Parse(List1[i]["GCL"].ToString()).ToString("F2")));
                    temprow.CreateCell(5).SetCellValue(double.Parse(double.Parse(List1[i]["RGDJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(6).SetCellValue(double.Parse(double.Parse(List1[i]["RGHJ"].ToString()).ToString("F2")));
                    RgSum += double.Parse(List1[i]["RGHJ"].ToString());
                    temprow.CreateCell(7).SetCellValue(double.Parse(double.Parse(List1[i]["CLDJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(8).SetCellValue(double.Parse(double.Parse(List1[i]["CLHJ"].ToString()).ToString("F2")));
                    ClSum += double.Parse(List1[i]["CLHJ"].ToString());
                    temprow.CreateCell(9).SetCellValue(double.Parse(double.Parse(List1[i]["ZJ"].ToString()).ToString("F2")));
                    temprow.CreateCell(10).SetCellValue(List1[i]["Memo"].ToString());
                    taxSum += double.Parse(double.Parse(List1[i]["ZJ"].ToString()).ToString("F2"));
                }
                for (int i = 0; i < List1.Count; i++)
                {
                    for (int j = 1; j < 11; j++)
                    {
                        commCell = Sheet1.GetRow(i + tempRow).GetCell(j);
                        //CommCellStyle(book, commCell);
                        commCell.CellStyle = fCellStyle;
                    }
                }
                //添加税金行
                IRow row3 = Sheet1.CreateRow(currRow);
                currRow++;
                row3.Height = 450;
                row3.CreateCell(0).SetCellValue(List1.Count + 1);
                row3.CreateCell(1).SetCellValue("税金");
                row3.CreateCell(2).SetCellValue("");
                row3.CreateCell(3).SetCellValue("元");
                row3.CreateCell(4).SetCellValue(double.Parse(taxSum.ToString("F2")));
                row3.CreateCell(5).SetCellValue(double.Parse(Map1["TaxRate"].ToString()).ToString("P"));

                double temptax = double.Parse(Map1["TaxRate"].ToString());
                row3.CreateCell(6).SetCellValue(double.Parse((taxSum * temptax).ToString("F2")));
                row3.CreateCell(7).SetCellValue("");
                row3.CreateCell(8).SetCellValue("");
                row3.CreateCell(9).SetCellValue(double.Parse((taxSum * temptax).ToString("F2")));
                row3.CreateCell(10).SetCellValue("");
                for (int i = 1; i < 11; i++)
                {
                    commCell = Sheet1.GetRow(currRow - 1).GetCell(i);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }
                //增加合计
                IRow row4 = Sheet1.CreateRow(currRow);
                currRow++;
                row4.Height = 450;
                row4.CreateCell(0).SetCellValue("合计");
                row4.CreateCell(1).SetCellValue("");
                row4.CreateCell(2).SetCellValue("");
                row4.CreateCell(3).SetCellValue("");
                row4.CreateCell(4).SetCellValue("");
                row4.CreateCell(5).SetCellValue("");
                row4.CreateCell(6).SetCellValue(RgSum);
                row4.CreateCell(7).SetCellValue("");
                row4.CreateCell(8).SetCellValue(ClSum);
                row4.CreateCell(9).SetCellValue(double.Parse((RgSum + ClSum + taxSum * temptax).ToString("F2")));
                row4.CreateCell(10).SetCellValue("");

                Sheet1.AddMergedRegion(new CellRangeAddress(currRow - 1, currRow - 1, 0, 5));
                for (int i = 0; i < 11; i++)
                {
                    commCell = Sheet1.GetRow(currRow - 1).GetCell(i);
                    //CommCellStyle(book, commCell);
                    commCell.CellStyle = fCellStyle;
                }

            }






            return Sheet1;
        }








            /// <summary>
            /// 合并单元格
            /// </summary>
            /// <param name="sheet">要合并单元格所在的sheet</param>
            /// <param name="rowstart">开始行的索引</param>
            /// <param name="rowend">结束行的索引</param>
            /// <param name="colstart">开始列的索引</param>
            /// <param name="colend">结束列的索引</param>
            public void SetCellRangeAddress(NPOI.SS.UserModel.ISheet sheet, int rowstart, int rowend, int colstart, int colend)
        {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
            sheet.AddMergedRegion(cellRangeAddress);
        }

        /// <summary>
        /// 设置标题单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="cell"></param>
        public void setCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 15 * 20;
            ffont.FontName = "宋体";
            ffont.Color =new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐

            //fCellStyle.BorderLeft = BorderStyle.Thin;
            //fCellStyle.BorderRight = BorderStyle.Thin;
            //fCellStyle.BorderTop = BorderStyle.Thin;
            //fCellStyle.BorderBottom = BorderStyle.Thin;
            cell.CellStyle = fCellStyle;
        }


        public void CommCellStyle(XSSFWorkbook workbook, ICell cell)
        {
            XSSFCellStyle fCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
            XSSFFont ffont = (XSSFFont)workbook.CreateFont();
            ffont.FontHeight = 220;
            ffont.FontName = "宋体";
            ffont.Color = new XSSFColor(Color.Black).Indexed;
            fCellStyle.SetFont(ffont);
            fCellStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;//垂直对齐
            fCellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;//水平对齐
            IDataFormat format = workbook.CreateDataFormat();
            fCellStyle.DataFormat = format.GetFormat("#,##0.00 ");//这是设置为3位数货币样式

            fCellStyle.BorderLeft = BorderStyle.Thin;
            fCellStyle.BorderRight = BorderStyle.Thin;
            fCellStyle.BorderTop = BorderStyle.Thin;
            fCellStyle.BorderBottom = BorderStyle.Thin;
            cell.CellStyle = fCellStyle;
        }

    }

    //新建类 重写Npoi流方法
    public class NpoiMemoryStream : MemoryStream
    {
        public NpoiMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }
}